package server.model.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.dto.UserDTO;

import model.dto.FileDTO;
import model.enums.UserStatusType;

public class FilesDAO {

	private static ArrayList<FileDTO> rstToFileDTO(ResultSet rst) throws SQLException{
		ArrayList<FileDTO> result=new ArrayList<FileDTO>();
		while(rst.next()){
			result.add(new FileDTO(rst.getInt("id"), rst.getInt("id_user"), rst.getString("filename"), rst.getString("extension"), rst.getLong("size")));
		}
		return result;
	}

	private static ArrayList<UserDTO> rstToUsersDTO(ResultSet rst) throws SQLException{
		ArrayList<UserDTO> result=new ArrayList<UserDTO>();
		while(rst.next()){
			result.add(new UserDTO(rst.getInt("id"), rst.getString("ip"), UserStatusType.valueOf(rst.getString("status").toUpperCase())));
		}
		return result;
	}

	private static ArrayList<FileDTO> getFiles(Connection dbConnection, String whereCondition, String groupBy){
		ArrayList<FileDTO> result=null;
		Statement stm=null;
		ResultSet rst=null;
		try{
			stm=dbConnection.createStatement();
			String query="SELECT DISTINCT * FROM files";
			if(!whereCondition.equals("")){
				query+=" WHERE (" + whereCondition + ")";
			}
			if(!groupBy.equals("")){
				query+=" GROUP BY " + groupBy;
			}
			rst=stm.executeQuery(query);
			result=rstToFileDTO(rst);
		} catch (SQLException e) {
			e.printStackTrace(System.err);
		}finally{
			try {
				rst.close();
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace(System.err);
			}
		}
		return result;
	}

	public static ArrayList<UserDTO> getUsersForFilteredFile(Connection dbConnection, Integer id, String filename, String extension, Integer id_user){
		String where="(1=1)";

		if(id!=0){
			where+=" AND(files.id=" + id + ")";
		}else{
			if(!filename.equals("")){
				where+=" AND (files.filename LIKE '%" + filename + "%')";
			}

			if(!extension.equals("")){
				where+=" AND(files.extension LIKE '%" + extension + "%')";
			}

			if(id_user!=0){
				where+="AND (files.id_user=" + id_user + ")";
			}
		}
		ArrayList<UserDTO> result=new ArrayList<UserDTO>();
		Statement stm=null;
		ResultSet rst=null;
		try{
			stm=dbConnection.createStatement();
			String query="SELECT files.id_user, files.id as id_file, files.filename, files.extension, files.size, users.status, users.ip, users.id FROM users  LEFT JOIN files ON users.id = files.id_user WHERE (users.status='ONLINE') AND " + where;
			System.out.println(query);
			rst=stm.executeQuery(query);
			result=rstToUsersDTO(rst);
		} catch (SQLException e) {
			e.printStackTrace(System.err);
		}finally{
			try {
				rst.close();
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace(System.err);
			}
		}
		return result;
	}

	public static ArrayList<FileDTO> getFilteredFiles(Connection dbConnection, Integer id, String filename, String extension){
		String where="(1=1)";

		if(id!=0){
			where+=" AND(files.id=" + id + ")";
		}else{
			if(!filename.equals("")){
				where+=" AND (files.filename LIKE '%" + filename + "%')";
			}

			if(!extension.equals("")){
				where+=" AND(files.extension LIKE '%" + extension + "%')";
			}
		}
		return getFiles(dbConnection, where, "files.id");
	}
}
